/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package basedatos;

import com.mysql.jdbc.CommunicationsException;
import java.sql.*;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;
import modulos.usuario.modelo.Medicina;

/**
 *
 * @author Miguel Angel
 */
public class CrearBaseDeDatos {

    public static Connection CONEXION;
    private static Statement sentencia;
    private static CrearBaseDeDatos con = null;
    static ResultSet rs;
    public static String host_bd = "localhost";
    public static String user = "root";
    public static String clave = "";

    public static void conectado(String contrasenia, String nombre_usuario, String dataBase, String host) {
        CONEXION = null;
        try {
            host_bd = host;
            user = nombre_usuario;
            clave = contrasenia;
            Class.forName("com.mysql.jdbc.Driver").newInstance();
            CONEXION = DriverManager.getConnection("jdbc:mysql://" + host_bd + ":3306/?", nombre_usuario, contrasenia);
            sentencia = CONEXION.createStatement();
            sentencia.executeUpdate("CREATE DATABASE IF NOT EXISTS " + dataBase);
            sentencia.executeUpdate("USE " + dataBase);
        } catch (ClassNotFoundException | InstantiationException | IllegalAccessException | SQLException e ) {           
            Logger.getLogger(CrearBaseDeDatos.class.getName()).log(Level.SEVERE, null, e);

        }
    }

    public static Connection conectate() {

        try {
            Class.forName("com.mysql.jdbc.Driver");
            CONEXION = DriverManager.getConnection("jdbc:mysql://" + host_bd + ":3306/dbMedicos_config", user, clave);
        } catch (SQLException | ClassNotFoundException ex) {
            Logger.getLogger(CrearBaseDeDatos.class.getName()).log(Level.SEVERE, null, ex);

        }
        return CONEXION;
    }

    public static void creaTablaMedicina() {
        try {
            sentencia = conectate().createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
            sentencia.executeUpdate("CREATE TABLE IF NOT EXISTS Medicina (idMedicina int(11) NOT NULL AUTO_INCREMENT,"
                    + "ejecucion int(11) NOT NULL,  inicial date DEFAULT NULL,mac varchar(255) DEFAULT NULL,serial varchar(255) DEFAULT NULL, PRIMARY KEY (idMedicina))" + "ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1");
            sentencia.close();
            conectate().close();
        } catch (Exception e) {
            Logger.getLogger(CrearBaseDeDatos.class.getName()).log(Level.SEVERE, null, e);
        }
    }

    public static void crearMedicina(Medicina med) {
        try {
            sentencia = conectate().createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
            rs = sentencia.executeQuery("SELECT * FROM Medicina");
            rs.moveToInsertRow();
            rs.updateString("mac", med.getMac());
            rs.updateInt("ejecucion", med.getEjecucion());
            rs.updateDate("inicial", new java.sql.Date(med.getInicial().getTime()));
            rs.updateString("serial", med.getSerial());
            rs.insertRow();
            rs.moveToCurrentRow();
            rs.close();
            sentencia.close();
            conectate().close();
        } catch (Exception e) {
            Logger.getLogger(CrearBaseDeDatos.class.getName()).log(Level.SEVERE, null, e);

        }
    }

    public static void modificar(Medicina med) throws SQLException {

        try {            
            sentencia = conectate().createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
            rs = sentencia.executeQuery("SELECT * FROM Medicina WHERE idMedicina= " + med.getIdMedicina());
            if (rs.next()) {
                rs.updateString("mac", med.getMac());
                rs.updateInt("ejecucion", med.getEjecucion());
                rs.updateDate("inicial", new java.sql.Date(med.getInicial().getTime()));
                rs.updateString("serial", med.getSerial());
                rs.updateRow();                
            } else {
                System.out.println( "No se encontró la medicina");
            }
            rs.close();
            sentencia.close();
            conectate().close();
        } catch (Exception e) {
            Logger.getLogger(CrearBaseDeDatos.class.getName()).log(Level.SEVERE, null, e);

        }
    }

    public static ArrayList<Medicina> listar() {
        ArrayList<Medicina> lista = new ArrayList<Medicina>();
        try {
            sentencia = conectate().createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
            String sql = "SELECT * FROM Medicina  ";
            rs = sentencia.executeQuery(sql);
            while (rs.next()) {
                Medicina p1 = new Medicina();
                p1.setIdMedicina(rs.getInt("idMedicina"));
                p1.setMac(rs.getString("mac"));
                p1.setEjecucion(rs.getInt("ejecucion"));
                p1.setInicial(new java.util.Date(rs.getDate("inicial").getTime()));
                p1.setSerial(rs.getString("serial"));
                lista.add(p1);
            }
            rs.close();
            sentencia.close();
            conectate().close();

        } catch (Exception ex) {
            Logger.getLogger(CrearBaseDeDatos.class.getName()).log(Level.SEVERE, null, ex);

        }
        return lista;
    }
}
